<?php

/* Copyright 2011 Nicky Thorne.
 * Filename - Database.class.php
 * Last modified: 27/07/2011
 *
 * Description - A useful Database Abstraction Layer written in PHP,   
 *               for handling MySQL connections. 
 *
 *
 * TODO:
 * - Implement port usage for mysqli::real_connect
 * - Add support for flags in mysql::real_connect
 * - Include support for mysqli_options();
 * - Look at possible extending mysqli class?
 * - Better handling of SQL conditions (WHERE clause)
 * - Add more robust comments?
 * - Possibly add rebound to connect() from isConnected()?
 * - Add Sanity check to WHERE conditions. (` and ')
 * - Make use of get_connection_status in disconnect()
 * - Add ability to modify SQL LIMIT
 *
 */


class Database {

    /* Class Variables
     * Used to establish a new MySQL Database connection.
     */
    private $DB_Host; // The Database Host
    private $DB_Name; // The Database Name
    private $DB_User; // Username of Database User
    private $DB_Password; // The Password of that User

    private $DB; // Database Object
    private $selectData;


    /* Class Constants
     * Used to display Error information to the user.
     */
    const ERR_SYNTAX = "Syntax: hostname, dbname, username, password";
    const ERR_CONNECT = "Could not establish database connection.";
    const ERR_EXCEPTION = "An Exception error has been catched. ";
    const ERR_INITIALISE = "Error initialising mysqli object. ";
    const ERR_PREPARE = "Couldn't prepare SQL statement. ";
    const ERR_QUERY_PREPARE = "Query Error: Couldn't prepare SQL Statement.";
    const ERR_INSERT_SYNTAX = "Insert Error: Syntax: insert(tablename, fieldsvalues).";
    const ERR_UPDATE_SYNTAX = "Update Error: Syntax: update(tablename, fieldvalues, condition).";
    const ERR_DELETE_SYNTAX = "Delete Error: Please see delete() function description.";
    const ERR_SELECT_SYNTAX = "Select Error: Syntax: select(tablename, columns, condition). Read function description for more information";


    /* Function - Database()
     * Scope = Public
     *
     * Class default constructor. Takes parameters and assigns them to class
     * variables. Then initalises MySQLi Object.
     *
     *
     * Parameters -
     *      $hostname - The database host (Required)
     *      $name - The database name (Required)
     *      $username - The database username (Required)
     *      $password - The database password (Required)
     *
     * Return Type -
     *      Boolean - Whether the database connection was successful or not.
     *
     */
    public function Database($hostname, $name, $username, $password) {

        if(isset($hostname, $name, $username, $password)) {

            $this->DB_Host = $hostname;
            $this->DB_Name = $name;
            $this->DB_User = $username;
            $this->DB_Password = $password;

            // Instantiate DB Handler
            $this->DB = new mysqli();

        } else {
            echo self::ERR_SYNTAX;
            return false;
        }

        if($this->initialise()) { return true; } else { return false; }

    }


    /* Function - initialise()
     * Scope = Private
     *
     * Initialises the Database handler. Is successful, goes ahed and
     * attempts to establish a connection with the database.
     *
     *
     * Parameters -
     *
     * Return Type -
     *      Boolean - Whether the Database was initialised
     *
     */
    private function initialise() {

        try {
            // Initialise Database handler
            $this->DB = mysqli_init();

        } catch (Exception $ex) {
            echo self::ERR_INITIALISE . "Message: $ex->getMessage()";
            return false;
        }

        // Connect to database
        $success = $this->connect();
        return $success;
    }


    /* Function - connect()
     * Scope = Private
     *
     * Attempts connection to Database using specified credentials.
     *
     * Parameters -
     *
     * Return Type -
     *      Boolean - Whether a connection was established or not.
     *
     */
    private function connect() {
        
        try {

            $flag = $this->DB->real_connect($this->DB_Host,
                                            $this->DB_User,
                                            $this->DB_Password,
                                            $this->DB_Name);

            if($flag) { return true; } else {

                echo self::ERR_CONNECT . mysqli_connect_errno() .
                        " Description: " . mysqli_connect_error();
                return false; 
               
            }


        } catch (Exception $ex) {
            echo self::ERR_CONNECT . "Message: $ex->getMessage()";
            return false;
        }

    }


    /* Function - insert()
     * Scope = Private
     *
     * Used to insert values into the database. A database connection has
     * to be established.
     * The $fieldvalues parameter must take the form of an associative array :
     * array("Field_Name" => "Value to insert");
     *
     *
     * Parameters -
     *      $table - The name of the table to insert into (Required)
     *      $fieldvalues - The fields and values to be inserted (Required)
     *
     * Return Type -
     *      Boolean - Whether insert was successful
     *
     */
    public function insert($table, $fieldvalues) {

        if($this->isConnected()) {

            if(isset($table, $fieldvalues)) {

                $SQL = "INSERT INTO $table (";

                $fields = array_keys($fieldvalues);
                $values = array_values($fieldvalues);

                // Iterate through columns
                for($i = 0; $i < sizeof($fields); $i++) {

                    $SQL .= "`$fields[$i]`";
                    $SQL .= ($fields[$i] != end($fields)) ? ", " : ")";
                   
                }

                $SQL .= " VALUES (";

                // Iterate through values
                for($i = 0; $i < sizeof($values); $i++) {

                    $SQL .= (is_numeric($values[$i])) ? $values[$i] : "'$values[$i]'";
                    $SQL .= ($values[$i] != end($values)) ? ", " : ")";

                }

                return $this->query($SQL);


            } else {
                echo self::ERR_INSERT_SYNTAX;
                return false;
            }

        }
    }


    /* Function - update()
     * Scope = Private
     *
     * Used to update entried in the database.
     * The $fieldvalues parameter must take the form of an associative array :
     * array("Field_Name" => "Value to insert");
     *
     *      *
     * Parameters -
     *      $table - The table to update (Required)
     *      $fieldvalues - The fields to be updated, and their
     *                     corresponding values (Required)
     *      $condition - The WHERE clause condition.
     *
     * Return Type -
     *      Boolean - Whether the update was successful or not.
     *
     */
    public function update($table, $fieldvalues, $condition = false) {

        if($this->isConnected()) {

            if(isset($table, $fieldvalues)) {

                $SQL = "UPDATE $table SET ";

                $fields = array_keys($fieldvalues);
                $values = array_values($fieldvalues);

                if(sizeof($fields) == sizeof($values)) {

                    foreach($fields as $field) {
                        $position = 0;

                        $SQL .= "`$field`='$values[$position]'";
                        $SQL .= (!$field == end($fields)) ? ", " : "";

                        $position++;
                    }

                    if($condition) {
                        $SQL .= " WHERE $condition";
                    }

                    $this->query(($SQL));
                    return $this->getRowNum();


                } else {
                    echo self::ERR_FIELD_VALUE_MISMATCH;
                    return false;
                }

            } else {
                echo self::ERR_UPDATE_SYNTAX;
                return false;
            }

        }
    }


    /* Function - delete()
     * Scope = Private
     *
     * Used to delete rows or values from the database. The $row parameter must
     * be specified if the whole row is to be deleted. If not, it can be ommitted.
     * The $condition parameter does not need to be speficied, unless you have
     * a condition to be used.
     *
     *      *
     * Parameters -
     *      $table - The table to update (Required)
     *      $condition - The WHERE clause condition.
     *      $row - Whether a row is to be deleted
     *
     * Return Type -
     *      Boolean - Whether the delete was successful or not.
     *
     */
    public function delete($table, $condition = false) {

        if($this->isConnected()) {

            if(isset($table)) {

                $SQL = "DELETE FROM $table";

                if(isset($condition)) {
                    $SQL .= " WHERE $condition";
                }

                if($this->getRowNum() > 0) {
                    return true;
                }

                return false;

            } else {
                echo self::ERR_DELETE_SYNTAX;
                return false;
            }
        
        }
    }
    

    /* Function - select()
     * Scope = Private
     *
     * Used to select data from a database.
     * The $fields parameter must take the form of a standard array:
     * array("field_1", "field_2")
     *
     * Parameters -
     *      $table - The table to update (Required)
     *      $fields - The columns to be selected.(Required)
     *      $condition - The WHERE clause condition.
     *
     * Return Type -
     *      Integer - The number of rows returned by the SELECT statement.
     *
     */
    public function select($table, $fields, $condition = false) {

        if($this->isConnected()) {

            if(isset($this->selectData) ) $this->selectData->free();

            if(isset($table, $fields)) {

                $SQL = "SELECT ";

                foreach($fields as $column) {
                    $SQL .= "`$column`";
                    $SQL .= ($column != end($fields)) ? ", " : " ";
                }

                $SQL .= "FROM $table ";             
                $SQL .= ($condition) ? "WHERE $condition" : "LIMIT 0, 30";

                $this->selectData = $this->query($SQL);
                return $this->selectData->num_rows;


                return false;

            } else {
                echo self::ERR_SELECT_SYNTAX;
                return false;
            }
        
        } else {
            echo self::ERR_CONNECT;
            return false;
        }

    }


    /* Function - query()
     * Scope = Private
     *
     * Internal function that's used to execute the actual SQL statements
     * against the Database.
     *
     *      *
     * Parameters -
     *      $table - The table to update (Required)
     *      $fieldvalues - The fields to be updated, and their
     *                     corresponding values (Required)
     *      $condition - The WHERE clause condition.
     *
     * Return Type -
     *      Boolean            - Returns the result of the query.
     *      Associative Array
     *
     */
    private function query($statement) {
        
        $SQL = $statement;
        
        try {

            if($this->DB->prepare($SQL)) {
                $result = $this->DB->query($SQL);
            } else {

                echo self::ERR_QUERY_PREPARE;
                return false;
            }

            if(!$result) {
                 echo "Error # " . mysqli_connect_errno() .
                     " Description: " . mysqli_connect_error();

                 return false;
            }


            return $result;

        } catch (Exception $ex) {
            echo self::ERR_PREPARE . "Message: $ex->getMessage()";
            return false;
        } 
        
    }


    /* Function - disconnect()
     * Scope = Public
     *
     *
     * Used to disconnect an active database connection.
     * If an existing database connection isn't specified, then the current
     * connection is closed.
     *
     * Parameters -
     *      $DN - A Database link resource
     *
     * Return Type -
     *      Boolean - Whether the connection was closed.
     *
     */
    public function disconnect($DB = null) {

        if(!is_Null($DB)) {
            $DB->close();
        } else {
            $this->DB->close();
            $this->DB = NULL;
        }

        return true;
        
    }

    
    /* Function - isConnected()
     * Scope = Public
     *
     *
     * Check if the database connection is active.
     *
     *
     * Return Type -
     *      Boolean - Is the connection is active or not.
     *
     */
    public function isConnected(){
        
        if(!is_null(($this->DB))) {
            if($this->DB->ping()) {
                return true;
            }
        } else { return false; }

    }


    public function getRowNum() {
        if($this->DB->affected_rows > 0) {
            return $this->DB->affected_rows;
        }
        
        return 0;
    }


    /* Function - getData()
     * Scope - Public
     * Description -
     *      Get's the data returned by a previously executed SELECT statement.
     *
     * Return Type - 
     *      Boolean - Is the connection is active or not.
     *
     */
    public function getData() {
        if(isset($this->selectData)) {
            return $this->selectData->fetch_assoc();
        }
    }


    /* Function - getDataAtRow()
     * Scope = Public
     * Description -
     *      First checks that a successful SELECT query has been executed, then
     *      sets the buffered result pointer to the value of $row, and then
     *      fetches that particular row.
     *
     * Parameters -
     *      (int)$row - The row number
     *
     * Return Type -
     *      Array of Strings - The data
     *
     */
    public function getDataAtRow($row) {
        if(isset($this->selectData, $row)
                && is_int($row)) {
            $this->selectData->data_seek(($row));
            return $this->selectData->fetch_row();
        }
    }


    /* Function - getNumRows()
     * Scope = Public
     * Description -
     *      First checks that a successsful SELECT query has been executed, then
     *      get's the number of rows returned by the query.
     *
     * Return Type -
     *      int - The number of rows
     *
     */
    public function getNumRows() {
        if(isset($this->selectData)) {
            return $this->selectData->num_rows;
        }
    }

    /* Function - getDatabase()
     * Scope = Public
     *
     *
     * Used to GET the database object.
     *
     *
     * Return Type -
     *      Link Resouce - The Database Object.
     *
     */
    public function getDatabase() {
        return $this->DB;
    }


}


?>